package com.jackie.tongzhuang.DataBase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.jackie.tongzhuang.Models.FenQi;
import com.jackie.tongzhuang.Models.GongHuoShang;
import com.jackie.tongzhuang.Models.JinHuoDan;
import com.jackie.tongzhuang.Models.YinHangKa;

import java.util.ArrayList;
import java.util.List;

public class DbClass {

    static Context context;
    static DbHelper dbHelper;
    public DbClass(Context _context){
        context = _context;
        dbHelper = new DbHelper(context);
    }

    /**
     * 根据表名插入数据
     * @param tableName  待插入数据的表名
     * @param values  待插入的数据字段和值
     * @return  返回插入成功的新行Id
     */
    public static long insert(String tableName,ContentValues values){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        long rowId = db.insert(tableName,null,values);
        db.close();
        return rowId;
    }

    /**
     * 根据表名删除数据
     * @param tableName  待删除数据的表名
     * @param id  待删除的记录Id
     * @return  返回的删除记录数，1表示删除成功
     */
    public static int delete(String tableName,int id){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int rowId = db.delete(tableName,"Id = ?",new String[]{String.valueOf(id)});
        return rowId;
    }

    /**
     * 根据表名更新数据
     * @param tableName 待更新的表名
     * @param values  待更新的字段和值
     * @param id  需要更新的记录Id
     * @return  返回的更新记录数，1表示更新成功
     */
    public static int update(String tableName,ContentValues values,int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int rowId = db.update(tableName, values, "Id = ?", new String[]{String.valueOf(id)});
        return rowId;
    }

    /**
     * 获取表记录总数
     * @param tableName  待查询的表名
     * @return
     */
    public static long getTableCount(String tableName){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query(tableName,null,null,null,null,null,null);
        int rowCount = cursor.getCount();
        cursor.close();
        db.close();
        return rowCount;
    }

    public static List<GongHuoShang> getGongHuoShangList(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Cursor cursor = db.query("GongHuoShang",null,null,null,null,null,null); // 查询数据
        List<GongHuoShang> ghss = new ArrayList<>();
        while(cursor.moveToNext()){
            GongHuoShang ghs = new GongHuoShang();
            ghs.setId(cursor.getInt(cursor.getColumnIndexOrThrow("Id")));
            ghs.setMingCheng(cursor.getString(cursor.getColumnIndexOrThrow("MingCheng")));
            ghs.setDiZhi(cursor.getString(cursor.getColumnIndexOrThrow("DiZhi")));
            ghs.setLianXiDianHua(cursor.getString(cursor.getColumnIndexOrThrow("LianXiDianHua")));
            ghs.setWeiXin(cursor.getString(cursor.getColumnIndexOrThrow("WeiXin")));
            ghss.add(ghs);
        }
        cursor.close();
        db.close();
        return ghss;
    }

    public static GongHuoShang getGongHuoShangById(int id){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query("GongHuoShang",null,"Id = ?",new String[]{String.valueOf(id)},null,null,null); // 查询数据
        GongHuoShang ghs = new GongHuoShang();
        if(cursor.getCount()>0) {
            while (cursor.moveToNext()) {
                ghs.setId(cursor.getInt(cursor.getColumnIndexOrThrow("Id")));
                ghs.setMingCheng(cursor.getString(cursor.getColumnIndexOrThrow("MingCheng")));
                ghs.setDiZhi(cursor.getString(cursor.getColumnIndexOrThrow("DiZhi")));
                ghs.setLianXiDianHua(cursor.getString(cursor.getColumnIndexOrThrow("LianXiDianHua")));
                ghs.setWeiXin(cursor.getString(cursor.getColumnIndexOrThrow("WeiXin")));
            }
        }
        cursor.close();
        db.close();
        return ghs;
    }

    public static YinHangKa getYinHangKa(int id){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query("YinHangKa",null,"Id=?",new String[]{String.valueOf(id)},null,null,null);
        YinHangKa yhk = new YinHangKa();
        if(cursor.getCount()>0){
            while(cursor.moveToNext()){
                yhk.setId(cursor.getInt(cursor.getColumnIndexOrThrow("Id")));
                yhk.setMingCheng(cursor.getString(cursor.getColumnIndexOrThrow("MingCheng")));
                yhk.setKaHao(cursor.getString(cursor.getColumnIndexOrThrow("KaHao")));
                yhk.setYinHang(cursor.getString(cursor.getColumnIndexOrThrow("YinHang")));
                yhk.seteDu(cursor.getDouble(cursor.getColumnIndexOrThrow("EDu")));
                yhk.setKeYongEDu(cursor.getDouble(cursor.getColumnIndexOrThrow("KeYongEDu")));
            }
        }
        return yhk;
    }

    public static double getXiaQiYingHuan(String rq){
        double fqje = 0;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String query_Sql = "select sum(FenQiJinE) as je from FenQi where ChangHuanRiQi = '"+rq+"' and YiChangHuan = 0";
        Cursor cursor = db.rawQuery(query_Sql,null);
        if(cursor.getCount() > 0) {
            while(cursor.moveToNext()) {
                String je = cursor.getString(cursor.getColumnIndexOrThrow("je"));
                Log.v("msgg","=="+je+"--");
                if (je != null) {
                    fqje = Double.valueOf(je);
                }
            }
        }
        return fqje;
    }

    public FenQi getFenQiById(int id){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        FenQi fq = new FenQi();
        Cursor cursor = db.query("FenQi", null, "Id = ?", new String[]{String.valueOf(id)}, null, null, null);
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                fq = initFenQi(cursor,db);
            }
        }
        return fq;
    }

    public List<FenQi> getFenQiByData(String data) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<FenQi> fqs = new ArrayList<>();
        Cursor cursor = db.query("FenQi", null, "ChangHuanRiQi = ? and YiChangHuan = 0", new String[]{data}, null, null, " ChangHuanRiQi asc");
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                FenQi fq = initFenQi(cursor, db);
                fqs.add(fq);
            }
        }

        cursor.close();
        db.close();
        return fqs;
    }

    public double getHuanKuanEByJinHuoDanId(String jhdid,int ych) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query("FenQi", new String[]{"sum(FenQiJinE) as YiChangHuan"},
                "JinHuoDanid = ? and YiChangHuan = ?", new String[]{jhdid,String.valueOf(ych)},
                null, null, null);
        double yhke = 0;
        while (cursor.moveToNext()) {
            yhke = cursor.getDouble(cursor.getColumnIndexOrThrow("YiChangHuan"));
        }
        return yhke;
    }

    public List<FenQi> getFenQiByJinHuoDanId(String jhdid) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<FenQi> fqs = new ArrayList<>();
        Cursor cursor = db.query("FenQi", null, "JinHuoDanid = ?", new String[]{jhdid}, null, null, " ChangHuanRiQi asc");
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                FenQi fq = initFenQi(cursor, db);
                fqs.add(fq);
            }
        }
        cursor.close();
        db.close();
        return fqs;
    }

    private FenQi initFenQi(Cursor cursor,SQLiteDatabase db) {

        FenQi fq = new FenQi();
        fq.setId(cursor.getInt(cursor.getColumnIndexOrThrow("Id")));
        fq.setQiShu(cursor.getString(cursor.getColumnIndexOrThrow("QiShu")));
        fq.setFenQiRiQi(cursor.getString(cursor.getColumnIndexOrThrow("FenQiRiQi")));
        fq.setChangHuanRiQi(cursor.getString(cursor.getColumnIndexOrThrow("ChangHuanRiQi")));
        fq.setFenQiJinE(cursor.getDouble(cursor.getColumnIndexOrThrow("FenQiJinE")));
        fq.setChangHuanJinE(cursor.getDouble(cursor.getColumnIndexOrThrow("ChangHuanJinE")));
        fq.setJinHuoDanId(cursor.getInt(cursor.getColumnIndexOrThrow("JinHuoDanId")));
        fq.setYiChangHuan(cursor.getInt(cursor.getColumnIndexOrThrow("YiChangHuan")));
        fq.setBenJin(cursor.getDouble(cursor.getColumnIndexOrThrow("BenJin")));
        fq.setLiXi(cursor.getDouble(cursor.getColumnIndexOrThrow("LiXi")));
        Cursor jhdcur = db.query("JinHuoDan", null, "Id = ?",
                new String[]{String.valueOf(fq.getJinHuoDanId())}, null, null, null);
        JinHuoDan jhd = new JinHuoDan();
        if (jhdcur.getCount() > 0) {
            while (jhdcur.moveToNext()) {
                jhd.setJinHuoRiQi(jhdcur.getString(jhdcur.getColumnIndexOrThrow("JinHuoRiQi")));
                jhd.setJinHuoJinE(jhdcur.getDouble(jhdcur.getColumnIndexOrThrow("JinHuoJinE")));
                jhd.setLeiBieId(jhdcur.getInt(jhdcur.getColumnIndexOrThrow("LeiBieId")));
                jhd.setGongHuoShangId(jhdcur.getInt(jhdcur.getColumnIndexOrThrow("GongHuoShangId")));
                Cursor ghscur = db.query("GongHuoShang", null, "Id = ?",
                        new String[]{String.valueOf(jhd.getGongHuoShangId())}, null, null, null);
                GongHuoShang ghs = new GongHuoShang();
                if (ghscur.getCount() > 0) {
                    while (ghscur.moveToNext()) {
                        ghs.setLianXiDianHua(ghscur.getString(ghscur.getColumnIndexOrThrow("LianXiDianHua")));
                        ghs.setWeiXin(ghscur.getString(ghscur.getColumnIndexOrThrow("WeiXin")));
                        ghs.setDiZhi(ghscur.getString(ghscur.getColumnIndexOrThrow("DiZhi")));
                        ghs.setMingCheng(ghscur.getString(ghscur.getColumnIndexOrThrow("MingCheng")));
                    }
                }
                jhd.setGongHuoShang(ghs);
            }
        }
        fq.setJinHuoDan(jhd);
        return fq;
    }

    public List<JinHuoDan> getJinHuoDan(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<JinHuoDan> jhds = new ArrayList<>();
        Cursor cursor = db.query("JinHuoDan", null, null, null, null, null, "Id desc");
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                JinHuoDan jhd = new JinHuoDan();
                jhd.setId(cursor.getInt(cursor.getColumnIndexOrThrow("Id")));
                jhd.setJinHuoJinE(cursor.getDouble(cursor.getColumnIndexOrThrow("JinHuoJinE")));
                jhd.setJinHuoRiQi(cursor.getString(cursor.getColumnIndexOrThrow("JinHuoRiQi")));
                jhd.setGongHuoShangId(cursor.getInt(cursor.getColumnIndexOrThrow("GongHuoShangId")));
                jhd.setLeiBieId(cursor.getInt(cursor.getColumnIndexOrThrow("LeiBieId")));
                Cursor ghscur = db.query("GongHuoShang", null, "Id = ?",
                        new String[]{String.valueOf(jhd.getGongHuoShangId())}, null, null, null);
                GongHuoShang ghs = new GongHuoShang();
                if (ghscur.getCount() > 0) {
                    while (ghscur.moveToNext()) {
                        ghs.setMingCheng(ghscur.getString(ghscur.getColumnIndexOrThrow("MingCheng")));
                        Log.v("msgg",ghs.getMingCheng()+"==c");
                    }
                }
                jhd.setGongHuoShang(ghs);
                jhds.add(jhd);
            }
        }
        return jhds;
    }

    public List<JinHuoDan> getJinHuoDan(String lx) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<JinHuoDan> jhds = new ArrayList<>();
        String wheresql = "";
        switch (lx){
            case "all":
                wheresql = " ";
                break;
            case "whq":
                wheresql = " where fq.YiChangHuan = 0 ";
                break;
        }
        String sql = "select jhd.id,jhd.GongHuoShangId,jhd.JinHuoJinE,jhd.JinHuoRiQi,jhd.LeiBieId" +
                " from JinHuoDan jhd " +
                "left join fenqi fq on fq.JinHuoDanId = jhd.id " +
                wheresql +
                "group by fq.JinHuoDanId " +
                "order by jhd.id desc";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            JinHuoDan jhd = new JinHuoDan();
            jhd.setId(cursor.getInt(cursor.getColumnIndexOrThrow("Id")));
            jhd.setJinHuoJinE(cursor.getDouble(cursor.getColumnIndexOrThrow("JinHuoJinE")));
            jhd.setJinHuoRiQi(cursor.getString(cursor.getColumnIndexOrThrow("JinHuoRiQi")));
            jhd.setGongHuoShangId(cursor.getInt(cursor.getColumnIndexOrThrow("GongHuoShangId")));
            jhd.setLeiBieId(cursor.getInt(cursor.getColumnIndexOrThrow("LeiBieId")));
            Cursor ghscur = db.query("GongHuoShang", null, "Id = ?",
                    new String[]{String.valueOf(jhd.getGongHuoShangId())}, null, null, null);
            GongHuoShang ghs = new GongHuoShang();
            if (ghscur.getCount() > 0) {
                while (ghscur.moveToNext()) {
                    ghs.setMingCheng(ghscur.getString(ghscur.getColumnIndexOrThrow("MingCheng")));
                    Log.v("msgg",ghs.getMingCheng()+"==c");
                }
            }
            jhd.setGongHuoShang(ghs);
            jhds.add(jhd);
        }
        return jhds;
    }




}
